This disclosure relates generally to generating database sequences, and more particularly to generating database sequences in a replicated database environment.
When a database table contains a column that utilizes an auto-generated value (for example, identity, sequences, etc.), this value is usually generated by the database management system (DBMS). This is at odds with the general set of row data that is usually explicitly supplied by a user as part of an INSERT operation. Auto-generated values are frequently used to ensure the uniqueness of rows and are therefore often used as a PRIMARY KEY to the table.
Imagine a table called bookTable with two columns, bookID and bookName. When adding row data to bookTable it is desirable to ensure that each row is unique for referential integrity and consistency purposes. A situation may be envisaged where two books exist with the same name. Therefore what ensures the uniqueness of a book in the bookTable is the bookID. From a user perspective, it is not necessary to be concerned with the books' bookID, it only matters that they are different and as such represent two individual books. In this instance, a constraint may be assigned to the database which ensures that each insert into bookTable is provided with a unique ID and that the unique ID (bookID in this instance) need not be supplied as part of the INSERT operation.
Two consecutive INSERT operations may be executed:    $ INSERT INTO TABLE bookTable VALUES (“My Book”);    > bookID bookName    1 MyBook    $ INSERT INTO TABLE bookTable VALUES (“My Book”);    > bookID bookName    1 MyBook    2 MyBook
Handling even the trivial configurations poses a significant problem when replicating data. Today, when performing full active-active database replication i.e. where write workloads can be executed on both nodes, special care must be taken where auto-generated table data is concerned. The problem is illustrated in the most basic sense in FIG. 1.
FIG. 1 shows a replicated database system 100 with a first database 110 and a second database 120. The first and second databases 110, 120 both have a table T1 111, 121 with an automatically generated ID field 112, 122 and a name field 113, 123. In this example, the values inserted 114, 115 in the name fields 112, 122 differ as the first database 110 has the name “Sam” and the second database 120 has the name “Steve”. However, the automatically generated ID fields 112, 122 have the same value of “1” causing a conflict 101 in the replication.
The most simplistic form of the problem occurs when the following conditions exist: bidirectional replication is configured between one or more nodes; any value in the replicated table-set is auto-generated by the database system. Examples are integer based identity values and sequences; and an insert related operation is executed on the same table on more than one of the replicated nodes at approximately the same time, i.e. in a time less than the sum of the transmission time between nodes and the commit time per node.
The above scenario is highly likely in high transactional environments (“hot” tables) i.e. where writes are occurring at a high frequency on the same table on both replicated nodes at the same time (load balanced environment). Under these conditions collision recovery algorithms are required which ultimately degrade the transactional performance of all systems.
The problem is further exacerbated when the number of replicating database increases. A way to distribute an auto-generated value range is required via a means that can tolerate fault in any of the replicated node set.